********************************************************************************
* program to clean coingecko data
********************************************************************************
capture program drop clean_gecko_data
	program define clean_gecko_data
	clear
	import delimited "build/input/coingecko/`1'-usd-max_pseudo.csv", varnames(1) 
	split snapped_at
	gen date = date(snapped_at1, "YMD")
	format date %td
	keep date price market_cap total_volume
	order date price market_cap total_volume

	gen coin = upper("`1'")
	
	rename price p
	rename total_volume vol

	tsset date
	gen r = (p/l.p-1)*100
	
	gen face = market_cap/p
	gen d_face = d.face
	gen d_face_millions = (d.face)/10^6
	gen ln_face = ln(face)
	gen d_ln_face = d.ln_face
	gen d_ln_face_bps = d_ln_face*100*100
	gen face_percent_change = ((d_face/l.face))*100
	gen d_market_cap = d.market_cap
	gen ln_market_cap = ln(market_cap)
	gen d_ln_market_cap = d.ln_market_cap

	gen redemptions = -1 * d_face if d_face<0
	gen redemptions_millions = redemptions/10^6
	gen redemptions_share = (redemptions/ (l.market_cap/l.p))*100
	
	gen issuances = d_face if d_face>0
	gen issuances_millions = issuances/10^6
	gen issuances_share = (issuances/ (l.market_cap/l.p))*100
	
	gen ln_vol = ln(vol)
	gen d_ln_vol = d.ln_vol

	capture confirm file "build/output/crypto_daily_panel.dta"
		if _rc==0 {
		append using "build/output/crypto_daily_panel.dta"
		sort coin date
		
		save "build/output/crypto_daily_panel.dta", replace
		}
		else {
			sort coin date
			save "build/output/crypto_daily_panel.dta", replace
		}
end



********************************************************************************
* import coins
********************************************************************************

capture erase "build/output/crypto_daily_panel.dta"

// speculative
clean_gecko_data "btc"    
clean_gecko_data "eth"
clean_gecko_data "doge"
clean_gecko_data "shib"

// stablecoins
clean_gecko_data "busd"
clean_gecko_data "dai"
clean_gecko_data "lusd"
clean_gecko_data "usdc"
clean_gecko_data "usdt"
clean_gecko_data "ustc" // USD Terra "classic", e.g., the one that failed
clean_gecko_data "tusd" // trueUSD
clean_gecko_data "gusd"
clean_gecko_data "usdp"
clean_gecko_data "usdn"
clean_gecko_data "usdk"
clean_gecko_data "rsv"
clean_gecko_data "pax" // coingecko ticker is usdp, but that's different from "USDP stablecoin", so I manually renamed this
clean_gecko_data "musd"
clean_gecko_data "mim"
clean_gecko_data "husd"
clean_gecko_data "frax"
clean_gecko_data "alusd"


********************************************************************************
* save btc, eth, doge 24 hour returns
********************************************************************************
preserve
keep if inlist(coin, "BTC")
keep date r ln_vol d_ln_vol
rename r r_btc 
rename ln_vol ln_vol_btc
rename d_ln_vol d_ln_vol_btc
save "build/output/coingecko_btc_return.dta", replace
restore

preserve
keep if inlist(coin, "ETH")
keep date r ln_vol d_ln_vol
rename r r_eth
rename ln_vol ln_vol_eth
rename d_ln_vol d_ln_vol_eth
save "build/output/coingecko_eth_return.dta", replace
restore

preserve
keep if inlist(coin, "DOGE")
keep date r ln_vol d_ln_vol
rename r r_doge
rename ln_vol ln_vol_doge
rename d_ln_vol d_ln_vol_doge
save "build/output/coingecko_doge_return.dta", replace
restore


********************************************************************************
* identify stablecoins
********************************************************************************

gen stablecoin = 0 
replace stablecoin = 1 if inlist(coin, "BUSD", "DAI", "LUSD", "USDC", "USDT", "USTC", "TUSD", "GUSD", "USDP")
replace stablecoin = 1 if inlist(coin, "USDN", "USDK", "RSV", "PAX", "MUSD", "MIM", "HUSD", "FRAX", "ALUSD")
save "build/output/crypto_daily_panel.dta", replace
